<?php
// 数据库连接和初始化文件

class Database {
    private $host = 'localhost';
    private $dbname = 'ldw';
    private $username = 'ldw';
    private $password = 'ldwldwldw';
    private $pdo = null;
    
    public function __construct() {
        $this->connect();
        $this->createTables();
    }
    
    private function connect() {
        try {
            $dsn = "mysql:host={$this->host};charset=utf8mb4";
            $this->pdo = new PDO($dsn, $this->username, $this->password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
            ]);
            
            // 创建数据库（如果不存在）
            $this->pdo->exec("CREATE DATABASE IF NOT EXISTS `{$this->dbname}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            $this->pdo->exec("USE `{$this->dbname}`");
            
        } catch (PDOException $e) {
            throw new Exception('数据库连接失败: ' . $e->getMessage());
        }
    }
    
    private function createTables() {
        try {
            // 创建访客数据表
            $sql = "CREATE TABLE IF NOT EXISTS visitors (
                id VARCHAR(50) PRIMARY KEY,
                timestamp DATETIME NOT NULL,
                client_timestamp VARCHAR(50),
                ip_address VARCHAR(45),
                user_agent TEXT,
                referer TEXT,
                latitude DECIMAL(10, 8) NOT NULL,
                longitude DECIMAL(11, 8) NOT NULL,
                accuracy DECIMAL(10, 2),
                photo_filename VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_timestamp (timestamp),
                INDEX idx_ip (ip_address),
                INDEX idx_created (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            
            $this->pdo->exec($sql);
            
            // 创建访问日志表
            $sql = "CREATE TABLE IF NOT EXISTS access_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                visitor_id VARCHAR(50),
                ip_address VARCHAR(45),
                timestamp DATETIME NOT NULL,
                user_agent TEXT,
                referer TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (visitor_id) REFERENCES visitors(id) ON DELETE CASCADE,
                INDEX idx_visitor_id (visitor_id),
                INDEX idx_timestamp (timestamp),
                INDEX idx_ip (ip_address)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            
            $this->pdo->exec($sql);
            
        } catch (PDOException $e) {
            throw new Exception('创建数据表失败: ' . $e->getMessage());
        }
    }
    
    public function getPDO() {
        return $this->pdo;
    }
    
    public function insertVisitor($data) {
        try {
            $sql = "INSERT INTO visitors (
                id, timestamp, client_timestamp, ip_address, user_agent, referer,
                latitude, longitude, accuracy, photo_filename
            ) VALUES (
                :id, :timestamp, :client_timestamp, :ip_address, :user_agent, :referer,
                :latitude, :longitude, :accuracy, :photo_filename
            )";
            
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute($data);
            
        } catch (PDOException $e) {
            throw new Exception('插入访客数据失败: ' . $e->getMessage());
        }
    }
    
    public function insertAccessLog($data) {
        try {
            $sql = "INSERT INTO access_logs (
                visitor_id, ip_address, timestamp, user_agent, referer
            ) VALUES (
                :visitor_id, :ip_address, :timestamp, :user_agent, :referer
            )";
            
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute($data);
            
        } catch (PDOException $e) {
            throw new Exception('插入访问日志失败: ' . $e->getMessage());
        }
    }
    
    public function getAllVisitors($limit = null, $offset = 0) {
        try {
            $sql = "SELECT * FROM visitors ORDER BY timestamp DESC";
            if ($limit) {
                $sql .= " LIMIT :limit OFFSET :offset";
            }
            
            $stmt = $this->pdo->prepare($sql);
            if ($limit) {
                $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
                $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            }
            
            $stmt->execute();
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            throw new Exception('获取访客数据失败: ' . $e->getMessage());
        }
    }
    
    public function getVisitorStats() {
        try {
            $stats = [];
            
            // 总访客数
            $stmt = $this->pdo->query("SELECT COUNT(*) as total FROM visitors");
            $stats['total'] = $stmt->fetch()['total'];
            
            // 24小时内访客数
            $stmt = $this->pdo->query("SELECT COUNT(*) as count FROM visitors WHERE timestamp > DATE_SUB(NOW(), INTERVAL 24 HOUR)");
            $stats['last_24h'] = $stmt->fetch()['count'];
            
            // 7天内访客数
            $stmt = $this->pdo->query("SELECT COUNT(*) as count FROM visitors WHERE timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY)");
            $stats['last_7d'] = $stmt->fetch()['count'];
            
            // 今日访客数
            $stmt = $this->pdo->query("SELECT COUNT(*) as count FROM visitors WHERE DATE(timestamp) = CURDATE()");
            $stats['today'] = $stmt->fetch()['count'];
            
            return $stats;
            
        } catch (PDOException $e) {
            throw new Exception('获取统计数据失败: ' . $e->getMessage());
        }
    }
    
    public function getVisitorById($id) {
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM visitors WHERE id = :id");
            $stmt->execute(['id' => $id]);
            return $stmt->fetch();
            
        } catch (PDOException $e) {
            throw new Exception('获取访客信息失败: ' . $e->getMessage());
        }
    }
    
    public function deleteVisitor($id) {
        try {
            $stmt = $this->pdo->prepare("DELETE FROM visitors WHERE id = :id");
            return $stmt->execute(['id' => $id]);
            
        } catch (PDOException $e) {
            throw new Exception('删除访客数据失败: ' . $e->getMessage());
        }
    }
    
    public function cleanupOldData($days = 30) {
        try {
            $stmt = $this->pdo->prepare("DELETE FROM visitors WHERE timestamp < DATE_SUB(NOW(), INTERVAL :days DAY)");
            $stmt->execute(['days' => $days]);
            return $stmt->rowCount();
            
        } catch (PDOException $e) {
            throw new Exception('清理旧数据失败: ' . $e->getMessage());
        }
    }
    
    // 批量删除访客记录
    public function batchDeleteVisitors($ids) {
        if (empty($ids)) {
            return 0;
        }
        
        try {
            $placeholders = str_repeat('?,', count($ids) - 1) . '?';
            $sql = "DELETE FROM visitors WHERE id IN ($placeholders)";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($ids);
            return $stmt->rowCount();
            
        } catch (PDOException $e) {
            throw new Exception('批量删除失败: ' . $e->getMessage());
        }
    }
    
    // 获取访客数据（支持分页和筛选）
    public function getVisitorsWithFilter($page = 1, $limit = 50, $filters = []) {
        try {
            $offset = ($page - 1) * $limit;
            $where = [];
            $params = [];
            
            // 构建WHERE条件
            if (!empty($filters['start_date'])) {
                $where[] = "timestamp >= :start_date";
                $params['start_date'] = $filters['start_date'];
            }
            
            if (!empty($filters['end_date'])) {
                $where[] = "timestamp <= :end_date";
                $params['end_date'] = $filters['end_date'] . ' 23:59:59';
            }
            
            if (!empty($filters['ip_address'])) {
                $where[] = "ip_address LIKE :ip_address";
                $params['ip_address'] = '%' . $filters['ip_address'] . '%';
            }
            
            if (!empty($filters['has_photo'])) {
                if ($filters['has_photo'] === 'yes') {
                    $where[] = "photo_filename IS NOT NULL AND photo_filename != ''";
                } else {
                    $where[] = "(photo_filename IS NULL OR photo_filename = '')";
                }
            }
            
            $whereClause = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';
            
            // 获取数据
            $sql = "SELECT * FROM visitors $whereClause ORDER BY timestamp DESC LIMIT :limit OFFSET :offset";
            $stmt = $this->pdo->prepare($sql);
            
            foreach ($params as $key => $value) {
                $stmt->bindValue(":$key", $value);
            }
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            
            $stmt->execute();
            $visitors = $stmt->fetchAll();
            
            // 获取总数
            $countSql = "SELECT COUNT(*) as total FROM visitors $whereClause";
            $countStmt = $this->pdo->prepare($countSql);
            foreach ($params as $key => $value) {
                $countStmt->bindValue(":$key", $value);
            }
            $countStmt->execute();
            $total = $countStmt->fetch()['total'];
            
            return [
                'data' => $visitors,
                'total' => $total,
                'page' => $page,
                'limit' => $limit,
                'pages' => ceil($total / $limit)
            ];
            
        } catch (PDOException $e) {
            throw new Exception('获取筛选数据失败: ' . $e->getMessage());
        }
    }
    
    // 获取数据库统计信息
    public function getDatabaseStats() {
        try {
            $stats = [];
            
            // 表大小
            $sql = "SELECT 
                        ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'size_mb',
                        table_rows
                    FROM information_schema.tables 
                    WHERE table_schema = :dbname AND table_name = 'visitors'";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['dbname' => $this->dbname]);
            $result = $stmt->fetch();
            
            $stats['table_size_mb'] = $result['size_mb'] ?? 0;
            $stats['estimated_rows'] = $result['table_rows'] ?? 0;
            
            // 最早和最新记录时间
            $stmt = $this->pdo->query("SELECT MIN(timestamp) as earliest, MAX(timestamp) as latest FROM visitors");
            $result = $stmt->fetch();
            $stats['earliest_record'] = $result['earliest'];
            $stats['latest_record'] = $result['latest'];
            
            // 有照片的记录数
            $stmt = $this->pdo->query("SELECT COUNT(*) as with_photos FROM visitors WHERE photo_filename IS NOT NULL AND photo_filename != ''");
            $stats['records_with_photos'] = $stmt->fetch()['with_photos'];
            
            return $stats;
            
        } catch (PDOException $e) {
            throw new Exception('获取数据库统计失败: ' . $e->getMessage());
        }
    }
    
    // 优化数据库表
    public function optimizeTable() {
        try {
            $this->pdo->exec("OPTIMIZE TABLE visitors");
            return true;
            
        } catch (PDOException $e) {
            throw new Exception('优化表失败: ' . $e->getMessage());
        }
    }
}
?>